select zipcode,sido,gugun,dong,bunji,seq from ZIPCODE where dong like  '%' || '대치' || '%'

select seq from ZIPCODE where dong like  '%' || '검색한값' || '%'
select seq from ZIPCODE where dong like  '%' || '대치' || '%'

select r.seq from room r,zipcode z where z.seq=r.seq and z.seq in(select seq from ZIPCODE where dong ='대치동')

select r.seq from room r,zipcode z where z.seq=r.seq and z.seq in(select seq from ZIPCODE where dong like'%' ||  '목동' || '%')
select r.seq from room r,zipcode z where z.seq=r.seq and z.seq in(select seq from ZIPCODE where dong like'%' ||  '목동' || '%')
select * from room where seq in(select r.seq from room r,zipcode z where z.seq=r.seq and z.seq in(select seq from ZIPCODE where dong like'%' ||  '목동' || '%'))
select * from room where seq in(select r.seq from room r,zipcode z where z.seq=r.seq and z.seq in(select seq from ZIPCODE where dong like'%' ||  '목동' || '%'))

select nation,seq,file_name,r.room_no,title,price_1day as price1,p.privacy_contents 
from room r,filelist f,privacy p where r.privacy=p.privacy_code 
and r.room_no=f.room_no and privacy_code 
in(select privacy from room) and seq
in(select seq from room where seq in(select r.seq from room r,zipcode z where z.seq=r.seq and z.seq in
(select seq from ZIPCODE where dong like'%' ||  '목동' || '%')))

select nation,seq,file_name,r.room_no,title,price_1day as price1,p.privacy_contents from filelist f,room r,privacy p 
where r.privacy=p.privacy_code 
and r.room_no=f.room_no and privacy_code 
in(select privacy from room) and seq
in(select seq from room where seq in(select r.seq from room r,zipcode z where z.seq=r.seq)) and
member_id ='admin1' 
select * from member
select * from message

select * from member m,room r where 
m.member_id = r.member_id and m.member_id='admin'
and r.seq in (select seq from room where seq in(select * from room r,zipcode z where z.seq=r.seq))

select * from member where member_id='admin'

select room_no as roomNo,accommodates,bedrooms,title,description,
phone,nation,price_1day as price1,price_7days as price7,price_30days as price30,
checkin_time as checkInTime,checkout_time as checkOutTime,add_detail 
as addDetail,seq,member_id as memberId,property_type from room r, zipcode z
where z.seq=r.seq and room_no=#roomNo#

select file_no as fileNo, file_name as fileName, room_no as roomNo from filelist where room_no='3'

select nation,seq,file_name,r.room_no,title,price_1day as price1,p.privacy_contents from filelist f,room r,privacy p 
where r.privacy=p.privacy_code 
and r.room_no=f.room_no and privacy_code 
in(select privacy from room) and seq
in(select seq from room where seq in(select r.seq from room r,zipcode z where z.seq=r.seq)) and
member_id ='admin' 

select nation,seq,r.room_no,title,price_1day as price1,p.privacy_contents 
from room r,privacy p where r.privacy=p.privacy_code and privacy_code 
in(select privacy from room) and seq
in(select seq from room where seq in(select r.seq from room r,zipcode z where z.seq=r.seq and z.seq in
(select seq from ZIPCODE where dong like'%' ||  '대치' || '%')))

select *from (select file_name from filelist order by DBMS_RANDOM.RANDOM())
where rownum <=4

select *from (select file_name
       from filelist
        order by DBMS_RANDOM.RANDOM()
       )
where rownum <= 4

select postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno,picture_filename from  member,postScript where member_id in(
select postScript_memberid from postScript where postScript_no=#postScript_no#
) and member_id = postScript_memberid
select * from postScript where postScript_roomno='111'
select * from postScript where postScript_no='12'

select room_no from room where 
select room_no from room r,filelist f where r.room_no = f.room_no and file_name='1343821754367.jpg'
select room_no from room where room_no in(select room_no from filelist where file_name='1343821948150.jpg')
select * from filelist
select * from reservation
select * from postScript
//이걸로 이용해야함
     211 1343821948150.jpg     139

select file_no as fileNo, file_name as fileName, room_no as roomNo from filelist where room_no='43'

update filelist set file_name =null where file_no in(select file_no as fileNo, file_name as fileName, room_no as roomNo from filelist where room_no='43')
update filelist set file_name as fileName=null where file_no as fileNo = '43'
update filelist set file_name = null where file_no = '43'
delete filelist set file_name = null where file_no= '43'\
delete file_name from filelist where file_no ='43'
update member set 
    picture_filename=null
	where id=#id#

	create table gogo(
	 n1umber varchar2(100);
	 go varchar2(100);	
	)
	create table filelist1(
file_no number,
file_name varchar2(100),
room_no number,
constraint file_no_pk1 primary key (file_no)
);
drop table filelist1
	이놈을 이용
	insert 
	select file_name from filelist where room_no='47'
	delete  filelist where file_name='1343716725428.jpg'
	 1343716725428.jpg
	 select * from filelist

	 select * from (select file_name
       from filelist order by DBMS_RANDOM.value
       )where rownum < 5

	 
	 
insert into filelist1(file_no,room_no,file_name) values ('1','2','15123.jpg');	
update filelist1 set file_name = null where file_no = '1'
ALTER TABLE filelist1 add(file_name varchar2(100))
alter table 

- 컬럼 변경
ALTER TABLE table_name MODIFY (COLUMN_NAME DATA_TYPE [제약조건])


  ※ 데이터가 존재하는 경우 변경 할 수 없다.
TRUNCATE TABLE filelist


update filelist set file_name = null where file_no =(select file_no as fileNo, file_name as fileName, room_no as roomNo from filelist where room_no='46')

update filelist set file_name = null where file_no ='51'

 - 컬럼 삭제 ★
ALTER TABLE filelist DROP COLUMN file_name
select * from filelist
칼럼 추가
① ALTER TABLE table_name ADD (COLUMN_NAME DATA_TYPE [제약조건]
alter table filelist add(file_name varchar2(100))
② ALTER TABLE table_name ADD CONSTRAINT 제약조건명 제약조건



select room_no as roomNo,accommodates,bedrooms,title,description,
phone,nation,price_1day as price1,price_7days as price7,price_30days as price30,
checkin_time as checkInTime,checkout_time as checkOutTime,add_detail 
as addDetail,seq,member_id as memberId,property_type from room, zipcode z
where seq=z.seq and room_no='41'

select * from room r,zipcode z where r.seq in
(select r.seq from room r,zipcode z where z.seq=r.seq) 
and r.room_no='41'
sele

//
select z.gugun,z.zipcode,z.sido,z.dong,z.bunji 
from room r,zipcode z where r.seq = z.seq and r.room_no='41'
//
select * from room r,zipcode z where z.seq=r.seq and r.member_id = 'admin'
in(select seq from room where seq in(select r.seq from room r,zipcode z where z.seq=r.seq))

select * from member,room r where member_id='admin' and r.seq
in(select seq from room where seq in(select r.seq from room r,zipcode z where z.seq=r.seq))

select seq from ZIPCODE where dong like  '%' || '대치' || '%'
select zipcode,sido,gugun,dong,bunji,seq from ZIPCODE where dong like  '%' ||  #dong# || '%'
drop table member cascade constraint;
drop table room cascade constraint;
drop table member cascade constraint;
drop table postScript cascade constraint;
select count(postScript_no) from postScript
//멤버 갯수
select count(member_id) from member
//방 갯수
select count(room_no) from room
select * from member
-- FILELIST 방 사진
create table filelist(
file_no number,
file_name varchar2(100) not null,
room_no number,
constraint file_no_pk primary key (file_no)
);

-- PROPERTY 객실형태
create table property(
property_type_code char(1),
property_type_contents varchar2(20) not null,
constraint property_type_code_pk primary key (property_type_code)
);

-- PRIVACY 렌탈 범위
create table privacy(
privacy_code char(1),
privacy_contents varchar2(20) not null,
constraint privacy_code_pk primary key (privacy_code)
);

-- REFUND 환불 정책
create table refund(
refund_policy_code char(1),
refund_policy_contents varchar2(20) not null,
constraint refund_policy_code_pk primary key (refund_policy_code)
);

select * from property

insert into property(property_type_code, property_type_contents) values ('A', '아파트');
insert into property(property_type_code, property_type_contents) values ('B', '단독주택');
insert into property(property_type_code, property_type_contents) values ('C', '민박');
insert into property(property_type_code, property_type_contents) values ('D', '원룸');
insert into property(property_type_code, property_type_contents) values ('E', '별장');
insert into property(property_type_code, property_type_contents) values ('F', '다세대주택');


select * from privacy

insert into privacy(privacy_code, privacy_contents) values ('A', '개인방');
insert into privacy(privacy_code, privacy_contents) values ('B', '다인실');
insert into privacy(privacy_code, privacy_contents) values ('C', '집/아파트 전체');


select * from REFUND

insert into refund(refund_policy_code, refund_policy_contents) values ('A', '엄격');
insert into refund(refund_policy_code, refund_policy_contents) values ('B', '보통');
insert into refund(refund_policy_code, refund_policy_contents) values ('C', '유연');

create sequence room_no;

alter table room 
add constraint seq_fk foreign key (seq) references zipcode (seq);
alter table room 
add constraint member_id_fk foreign key (member_id) references member (member_id);
alter table room 
add constraint property_type_fk foreign key (property_type) references property (property_type_code);
alter table room 
add constraint privacy_fk foreign key (privacy) references privacy (privacy_code);

alter table room 
add constraint refund_fk foreign key (refund) references refund (refund_policy_code);
drop table room cascade constraint;
create table room(
room_no number,
accommodates number(2) not null,
bedrooms number(2) not null,
title varchar2(50) not null,
description varchar2(4000) not null,
phone varchar2(13) not null,
nation varchar2(20) not null,
price_1day number not null,
price_7days number,
price_30days number,
checkin_time char(2) not null,
checkout_time char(2) not null,
seq varchar2(5),
member_id varchar2(30),
property_type char(1),
privacy char(1),
refund char(1),
add_detail varchar2(50),
constraint room_no_pk primary key (room_no)
);





create sequence file_no;
select property_type from room where room_no='21'
select property_type from room where room_no='21'
select * from privacy

//방번호로 환불정책 조회
select refund_policy_contents from refund where refund_policy_code=(select refund from room where room_no='21')
//방번호로 property 조회
select property_type_contents from property where property_type_code=(select property_type from room where room_no='21' )
//방번호로 privacy 조회
select privacy_contents from privacy where privacy_code=(select privacy from room where room_no='21')
//방번호로 파일_이름 조회
select file_name from filelist where room_no='21'
select * from filelist
select File_Name from filelist,room where room_no = (select room_no from room)
)

select File_Name from filelist,room where room_no(select privacy from room where room_no=#roomno#)
select File_Name from filelist where room_no in(select room_no from room)
select * from privacy

지금 해야 하는건 파일명/제목/정책/가격/번호가 나와야 한다. 
select file_name,room_no,title,price_1day as price1,privacy_contents from filelist,privacy,room where privacy_code in(select privacy from room)
select file_name,r.room_no,title,price_1day as price1,p.privacy_contents from room r,filelist f,privacy p where r.privacy=p.privacy_code and r.room_no=f.room_no and privacy_code in(select privacy from room)

select * from address
select * from room
select file_name,title,price_1day as price1,privacy_contents from filelist,privacy,room
select * from room
select privacy_contents from privacy where privacy_code in(select privacy from room)
select privacy_contents from privacy where 
select File_Name from filelist where room_no in(select room_no from room)
select room_no from room
select * from refund
//방을 검색하고 파일도 나와야함
select * from room,filelist where 
select * from property
select * from privacy

select * from ZIPCODE 
where dong like '%목동%' or sido like '%서울시%' or gugun like '%양천구%'


select * from filelist f,privacy p,room r where room_no=f.room_no and r.privacy =p.privacy_code 
select * from room
select room_no as roomNo,accommodates,bedrooms,title,description,
phone,nation,price_1day as price1,price_7days as price7,price_30days as price30,
checkin_time as checkInTime,checkout_time as checkOutTime,add_detail as addDetail,seq,member_id as memberId,property_type,privacy,refund from room where room_no='21'



drop table room

select * from
(select ceil(rownum/'2') page, postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno from
(select  * from postScript)	)where page ='2';


select * from
(select ceil(rownum/'2') page, postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno from
(select  * from postScript)	)where page ='1' and postScript_roomno='3' order by postScript_no asc

select * from (
select ceil(rownum/2)page,postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno from(
select * from postScript where postScript_roomno='3'
)) where page=3

select count(postScript_no) from postScript where postScript_roomno=#postScript_roomno#
select * from postScript where postScript_roomno='3'

select count(postScript_no) from postScript where postScript_roomno=3










"select page,no, title, writer, content, writedate, viewCount, refamilly, restep, relevel v from(select ceil(rownum/?) page, no, title, writer, content, writedate, viewCount, refamilly, restep, relevel from(select  no, title, writer, content, writedate, viewCount, refamilly, restep, relevel from board order by refamilly desc, restep asc)	)where page =?";
select ceil(rownum/'1') page, postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno from postScript
select * from postScript
관계설정
alter table room 
add constraint member_id_fk foreign key (member_id) references member (member_id);

alter table postScript 
add constraint fk_roomno foreign key(postScript_roomno) references room(room_no);


select picture_filename from  member,postScript where member_id in(#value#) and member_id = postScript_memberid where postScript_no='4'

select picture_filename from  member,postScript where member_id in('oop') and member_id = postScript_memberid
select * from room
select * from postScript
select * from member

insert into postScript(postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno) values('2','2','2','2','2','2','2')

create sequence postScript_no_seq
select postScript_no_seq from postScript
select postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno,picture_filename from  member,postScript where member_id in(
select postScript_memberid from postScript where postScript_no='283'
) and member_id = postScript_memberid

select postScript_no_seq.nextval from dual
select postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno,picture_filename from  member,postScript where member_id in(
select postScript_memberid from postScript where postScript_no=#value#
) and member_id = postScript_memberid

alter table postScript 
add constraint fk_roomno foreign key(postScript_roomno) references room(room_no);

alter table postScript 
add constraint fk_memberid foreign key(postScript_memberid) references member(member_id);

select picture_filename from  member,postScript where member_id in('oop') and member_id = postScript_memberid


select postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno,picture_filename from  
member m,postScript,room r where room_no='1' and  postScript_memberid = m.member_id and postScript_roomno = r.room_no

select room_no,accommodates,bedrooms,title,description,
phone,nation,price_1day,price_7days,price_30days,
checkin_time,checkout_time,seq,member_id,property_type,privacy,refund,add_detail from room where room_no='2'


select * from postScript where postScript_roomno='2'
select address_id from room
select * from room where room_no='2'

select * from member,room where room_no='1'
select * from postScript where postScript_roomno='1'

select * from
(select ceil(rownum/#perpage#) page, postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno from
(select  * from postScript)	)where page =#page# and postScript_roomno=#postScript_roomno# order by postScript_no asc




insert into room(room_no,accommodates,bedrooms,title,description,
phone,nation,price_1day,price_7days,price_30days,
checkin_time,checkout_time,seq,member_id,property_type,privacy,refund,add_detail  ) values
('17','2','3','4','5','6','7','8','9','10','11','12','13','admin1','A','B','C','C')
select * from member
select * from privacy
select * from refund
checkout_time char(2) not null,
seq varchar2(5),
member_id varchar2(30),
property_type char(1),
privacy char(1),
refund char(1),
add_detail varchar2(50),


drop table room
create table room(
room_no number,
member_id varchar2(30),
constraint room_no_pk primary key (room_no)
)
insert into room(room_no,member_id) values('1','2') 
select * from room
select * from room
select * from member
select * from postScript
postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno

select picture_filename from  member, postScript where member_id in('oop') and member_id = postScript_memberid
select postScript_no,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno,picture_filename from  member,postScript where member_id in(postScript_memberid) and member_id = postScript_memberid
select	picture_filename from member where member_id='admin'

select postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno,picture_filename from  member,postScript where member_id in(
select postScript_memberid from postScript where postScript_no='311'
) and member_id = postScript_memberid
select postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno from  
postScript where postScript_roomno='311'

drop table room
create table room(
room_no number,
accommodates number(2) not null,
bedrooms number(2) not null,
title varchar2(50) not null,
description varchar2(4000) not null,
phone varchar2(13) not null,
nation varchar2(20) not null,
price_1day number not null,
price_7days number,
price_30days number,
checkin_time char(2) not null,
checkout_time char(2) not null,
seq varchar2(5),
member_id varchar2(30),
property_type char(1),
privacy char(1),
refund char(1),
add_detail varchar2(50),
constraint room_no_pk primary key (room_no)
);

select * from member where 


 alter table postScript 
add constraint fk_memberid foreign key(postScript_memberid) references member(member_id);
create table member(
member_id varchar2(30) not null,
member_password varchar2(20) not null,
last_name varchar2(20) not null,
first_name varchar2(30) not null,
birthday varchar2(8) not null,
phone_no varchar2(13),
address varchar2(150),
regist_date varchar2(8) not null,
picture_filename varchar2(50),
member_gender char(1) not null,
constraint member_pk1 primary key(member_id)
);
select * from room
select * from member
drop table room cascade constraint;
drop table member cascade constraint;
drop table postScript cascade constraint;
create table postScript(
postScript_no number,
postScript_content varchar2(700) not null,
postScript_writedate varchar2(14) not null,
postScript_score number not null,
postScript_memberid varchar2(10) not null,
postScript_roomno number not null,
postScript_resno number not null,
constraint postScript_no primary key(postScript_no)
);


select * from
(select ceil(rownum/3) page, postScript_no,postScript_content,postScript_writedate,postScript_score,postScript_memberid,postScript_roomno,postScript_resno from
(select  * from postScript)	)where page =2 order by postScript_no asc
